<?
session_start();
error_reporting(E_ALL);
include('conn.php');

$ch = $_REQUEST['q'];
if($ch=='2'){
	$qry1="SELECT distinct custid from opbalmast where custid in(select custid from customermast where custtype='Vendor');";
	$qry = mysql_query($qry1);
	$data = array();
	$i = 0;
	while($rows = mysql_fetch_array($qry)){
		$qry2="SELECT custdesc from customermast where custid='".$rows['custid']."' ";
		$rows2 = mysql_fetch_array(mysql_query($qry2));
		$data[$i]=$rows2['custdesc'];
		$i++;
	}
	echo json_encode($data);
}
else{
//$custid = $_SESSION['custid'];
//$custid = 484; // rajesh- test
//$custid = 423; // AKBAR ONLINE
$selc = $_REQUEST['selc'];
$qry1="SELECT custid from customermast where custdesc ='$selc';";
$qry = mysql_query($qry1);
$rows = mysql_fetch_array($qry);
$custid=$rows['custid'];

//$custtype = $_SESSION['custtype'];
//$year = $_REQUEST['year'];  sum(sumpaidamt) as sumpaidamt,

 //$qry1 = "select sum(t.debitamt) - sum(t.creditamt) as outstanding, 
//		  o.custid, c.custdesc as customername, sum(amtreceived) as sumreceivedamt, 
//		  sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)) as remainingamt 
//		  from opbalmast o left join transactionmast t on t.custid = o.custid 
//		  left join customermast c on c.custid = o.custid
//		  group by o.custid,t.custid 
//		  order by o.custid ,t.custid"; 


///$qry1 ="select o.opbaldate,
//IF(c.custdesc = 'YATRA', sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)), NULL) 'YATRA',
//IF(c.custdesc = 'SPICEJET CORP', sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)), NULL) 'SPICEJET CORP',
//IF(c.custdesc = 'INDIGO CORP', sum(amtreceived) - (sum(t.debitamt) - sum(t.creditamt)), NULL) 'INDIGO CORP'
//from opbalmast o left join transactionmast t on t.custid = o.custid
//left join customermast c on c.custid = o.custid
//group by o.custid,t.custid
//order by o.custid ,t.custid";
//echo $qry1;

//$qry1 ="SET @runtot:=0";
//$qry1 =$qry1."SELECT q1.d, q1.custid, (@runtot := @runtot + q1.c) AS rt FROM (SELECT t1.opbaldate AS d, t1.custid, sum(amt) AS c FROM (select opbaldate,custid, case when amtpaid > 0 then -1 * amtpaid when amtreceived > 0 then amtreceived else 0 end as amt from opbalmast) t1 GROUP BY custid,opbaldate) AS q1";
/* $qry1 ="SELECT q1.d, q1.custid, (@runtot := @runtot + q1.c) AS rt FROM (SELECT t1.opbaldate AS d, t1.custid, sum(amt) AS c FROM (select opbaldate,custid, case when amtpaid > 0 then -1 * amtpaid when amtreceived > 0 then amtreceived else 0 end as amt from opbalmast where custid=466) t1 GROUP BY custid,opbaldate) AS q1";
$qry = mysql_query($qry1);
$data = array();
$qry = mysql_query($qry1);
$data = array();
$i=1; */
/* while($rows = mysql_fetch_array($qry))
{
				$data[] = array("Sr_NO" => $i++,
					  "outstanding" => $rows['outstanding'],
					 "custid" => $rows['custid'],
					 "customername" => $rows['customername'],
                     "sumreceivedamt" => $rows['sumreceivedamt'],	
					 "sumpaidamt" => $rows['sumpaidamt'],	
					 "renamingamt" => $rows['rt']
	          );
}
echo json_encode($data);
?> */

	// $qry2 = "SET @runtot=0;";
	// mysql_query($qry2);
	
	//$qry1="SELECT n.custid,n.opbaldate, SUM(o.order_total) As running_total FROM (SELECT custid, opbaldate , sum(amtreceived)-SUM(amtpaid) As order_total FROM opbalmast GROUP BY custid,opbaldate ORDER BY custid,opbaldate) n INNER JOIN (SELECT custid, opbaldate, sum(amtreceived)-SUM(amtpaid) As order_total FROM opbalmast GROUP BY custid,opbaldate ORDER BY custid,opbaldate) o ON (n.custid = o.custid) and (n.opbaldate >= o.opbaldate) GROUP BY n.custid, n.opbaldate ORDER BY n.custid, n.opbaldate ";
	
	
	
	$qry1="SELECT n.custid,n.opbaldate, SUM(o.amount) As running_total FROM (select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
(select trndate as opbaldate,netrate*-1 as debit,venid  as custid , 't' as flag
from transactionmast where venid = $custid and netrate >0 and trndate >= (select min(opbaldate) from opbalmast where custid = $custid and amtreceived > 0) 
union
(select opbaldate,amtreceived,custid, 'c' as flag from opbalmast where custid = $custid
and amtreceived >0 )
union
(select opbaldate,amtpaid * -1 as debit,custid, 'd' as flag from opbalmast where custid = $custid and amtpaid >0)
order by custid,opbaldate) tt
group by custid,opbaldate
) n INNER JOIN (select tt.opbaldate, sum(tt.debit) as amount, tt.custid from
(select trndate as opbaldate,netrate*-1 as debit,venid  as custid , 't' as flag
from transactionmast where venid = $custid and netrate >0 and trndate >= (select min(opbaldate) from opbalmast where custid = $custid and amtreceived > 0) 
union
(select opbaldate,amtreceived,custid, 'c' as flag from opbalmast where custid = $custid
and amtreceived >0 )
union
(select opbaldate,amtpaid * -1 as debit,custid, 'd' as flag from opbalmast where custid = $custid and amtpaid >0)
order by custid,opbaldate) tt
group by custid,opbaldate
)
o ON (n.custid = o.custid) and (n.opbaldate >= o.opbaldate) GROUP BY n.custid, n.opbaldate";

	$qry = mysql_query($qry1);
	$data = array();
	$i=1;	
	
	while($rows = mysql_fetch_array($qry))
	{
		$qrycust = "select custdesc from customermast where custid='".$rows['custid']."';";
		$rowcust = mysql_fetch_array(mysql_query($qrycust));
		
		$data[] = array("Sr_NO" => $i++,        			
						"rqdate" => $rows['opbaldate'],
						"custid" => $rows['custid'],
						"customer" => $rowcust['custdesc'],
						"runningtotal" =>$rows['running_total']
						);
	}
	echo json_encode($data);
}
?>